//
//  HSQLManager.m
//  HMusicPlayer
//
//  Created by Hament on 14/9/5.
//  Copyright (c) 2014年 HJ. All rights reserved.
//

#import "HSQLManager.h"
#import "HCoreConfig.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "HPlaylistItem.h"
#import "HSongItem.h"



@interface HSQLManager ()

@end

@implementation HSQLManager



#pragma mark singleon

static HSQLManager * sqlmg=nil;
+(HSQLManager *)shareSqlMg{
    
    @synchronized( self )
    {
        if (sqlmg==nil) {
            sqlmg=[[HSQLManager alloc] init];
            
        }
    }
    return sqlmg;
}



-(id)init{

    self=[super init];
    
    if (self) {
        self.songDB = [FMDatabase databaseWithPath:songDBPath];
    }
    
    return self;
}


-(BOOL)openSongDB{

  return  [self.songDB open];
}

-(BOOL)closeSongDB{

  return  [self.songDB close];
}


#pragma  mark -----------------通用---------
//从表中删除纪录
+(BOOL)deleteRecordIntable:(NSString*)tablename andID:(NSString*)syncID{




    return NO;

}





//从表中根据精确条件删除
+(BOOL)deleteRecordIntable:(NSString*)tablename WithDic:(NSDictionary*)paraDic{




return NO;
}



//update 通用



#pragma  mark -------------查询---------
//获取db记录的版本号
+(NSString*)getappVersionInDB{
    NSString * version=@"";
    
if ([[HSQLManager shareSqlMg].songDB open]) {
          NSString * sql=@"SELECT version FROM AppVersion where ID=1";
            FMResultSet * rs = [[HSQLManager shareSqlMg].songDB executeQuery:sql];
            while ([rs next]) {
                version =CHECKSTR([rs stringForColumn:@"version"]);
            }

    [[HSQLManager shareSqlMg].songDB close];
}
    
    return version;
}



//获取自定义播放列表
+(NSMutableArray*)getPlayListFromSongDB{

    NSMutableArray * arr=[NSMutableArray arrayWithCapacity:10];
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        NSString * sql=@"SELECT * FROM Playlist";
        FMResultSet * rs = [[HSQLManager shareSqlMg].songDB executeQuery:sql];
        
        while ([rs next]) {
            HPlaylistItem * pit=[[HPlaylistItem alloc] init];
            
            pit.name =CHECKSTR([rs stringForColumn:@"name"]);
            pit.syncid =[rs stringForColumn:@"syncID"];
            pit.folderPath =CHECKSTR([rs stringForColumn:@"folderPath"]);
            [arr addObject:pit];
        }
        
        [[HSQLManager shareSqlMg].songDB close];
    }

    return arr;
}



//获取歌曲
+(NSMutableArray*)getSongsByPlaylistItem:(HPlaylistItem*)pitem{

    NSMutableArray * arr=[NSMutableArray arrayWithCapacity:10];
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        NSString * sql=nil;
        
        //获取itunes
        if ([pitem.syncid isEqualToString:itunesSyncid]) {
            
            
          NSArray* iarr= [[HCoreConfig shareConfig] getItunesSongs];
          NSArray * sortSonglyarr;
            
            //根据排序获取
            if ([[HCoreConfig shareConfig].currentSortType isEqualToString:SortByName]) {
               
                 sortSonglyarr= [iarr sortedArrayUsingComparator:^(HSongItem * obj1 ,HSongItem* obj2) {
                    if ([obj1.songName compare:obj2.songName]==NSOrderedAscending)
                        return  NSOrderedAscending;
                    else
                        return NSOrderedDescending;
                    return NSOrderedSame;
                }];

            } else {
                sortSonglyarr= [iarr sortedArrayUsingComparator:^(HSongItem * obj1 ,HSongItem* obj2) {
                    if (obj1.syncid.doubleValue > obj2.syncid.doubleValue)
                        return NSOrderedDescending;
                    else
                        return NSOrderedAscending;
                    return NSOrderedSame;
                }];
            }
            
            [arr addObjectsFromArray:sortSonglyarr];
            
            [[HSQLManager shareSqlMg].songDB close];
            return arr;
            
        }//获取在线听过的歌曲
         else if ([pitem.syncid isEqualToString:onlineSyncid]) {
            
            
            [[HSQLManager shareSqlMg].songDB close];
            return arr;
            
         }//获取喜爱的,需要除重
         else if ([pitem.syncid isEqualToString:likeSyncid]) {
             
             //为了除重,把ituneslike的歌曲写到数据库里面去, 用likesyncid作为 playlistsyncid
             NSArray * itunesLikearr=[[HCoreConfig shareConfig] getItunesFavouriteSongs];

         
             //1.先delete一次
             NSString* dsql=@"delete from Song where playListSyncID='likeSyncid'";
              [[HSQLManager shareSqlMg].songDB executeUpdate:dsql];
             
             //2.插入itunes like song
             
             [[HSQLManager shareSqlMg].songDB beginTransaction];
             for(HSongItem * item in itunesLikearr){
             
             [[HSQLManager shareSqlMg].songDB executeUpdate:@"insert into Song (songType,localPath,urlPath,songName,songDuration,lyricsLocalPath,albumLocalPath,islike,playListSyncID,syncID,trackInfo,album,artist) values (?, ?, ?, ?, ?,?, ?, ?, ?, ?,?,?,?)" ,
              CHECKSTR(item.songType),
              CHECKSTR(item.localPath),
              CHECKSTR(item.urlPath),
              CHECKSTR(item.songName),
              [NSString stringWithFormat:@"%ld",(long)item.songDuration]  ,
              CHECKSTR(item.lyricsLocalPath),
              CHECKSTR(item.albumLocalPath),
              [NSNumber numberWithBool:item.islike],
              likeSyncid,
              item.syncid,
              CHECKSTR(item.trackInfo),
              CHECKSTR(item.album),
              CHECKSTR(item.artist)];
                 
             }
             
             [[HSQLManager shareSqlMg].songDB commit];
             
             
             //DISTINCT 是对整条数据是否重复做判断
             //             sql=@"select DISTINCT localPath,ID,songType,urlPath,songName,songDuration,lyricsLocalPath,albumLocalPath,islike,playListSyncID,syncID,trackInfo from  Song where islike=1";
             
             //根据排序获取
             if ([[HCoreConfig shareConfig].currentSortType isEqualToString:SortByName]) {
                  sql=@"select * from  Song where islike=1  GROUP BY localPath ORDER BY songName";
             } else {
                  sql=@"select * from  Song where islike=1  GROUP BY localPath ORDER BY ID";
             }
             
             
             
         } else {
         
             //根据排序获取
             if ([[HCoreConfig shareConfig].currentSortType isEqualToString:SortByName]) {
                 sql=[NSString stringWithFormat:@"select * from Song where playListSyncID='%@' ORDER BY songName",pitem.syncid];
             } else {
                 sql=[NSString stringWithFormat:@"select * from Song where playListSyncID='%@' ORDER BY ID",pitem.syncid];
             }
         
         
         }
        
        FMResultSet * rs = [[HSQLManager shareSqlMg].songDB executeQuery:sql];
        
        while ([rs next]) {
            HSongItem * sit=[[HSongItem alloc] initWithResultset:rs andPlaylistItem:pitem];
            [arr addObject:sit];
        }
        
        [[HSQLManager shareSqlMg].songDB close];
    }
    
    return arr;



}




//寻找like的歌曲是否有来自itunes的,因为之前某些结果被group by掉
+(BOOL)findLikeSongsisFromItunes:(HSongItem*)songitem{

    BOOL res=NO;
    FMResultSet * rs = [[HSQLManager shareSqlMg].songDB executeQuery:@"select ID from Song where playListSyncID='likeSyncid' AND islike=1 AND localPath=?",songitem.localPath];
    
    while ([rs next]) {
        res=YES;
        break;
    }

    return res;
}


//获取歌曲的歌词路径
+(NSString*)getLyricPathWithSongItem:(HSongItem*)songItem{


    NSString * path=nil;
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        //获取本地歌曲文件的歌词
       
        FMResultSet * rs =nil;
        if (![songItem.localPath isEqualToString:@""]) {
            
             rs= [[HSQLManager shareSqlMg].songDB executeQuery:@"select lyricsLocalPath from SongInfo where localPath=? ",songItem.localPath];
        } else if (![songItem.urlPath isEqualToString:@""]){
        
          rs= [[HSQLManager shareSqlMg].songDB executeQuery:@"select lyricsLocalPath from SongInfo where urlPath=? ",songItem.urlPath];
        }
        
       
        if (rs) {
            while ([rs next]) {
                path=CHECKSTR([rs stringForColumn:@"lyricsLocalPath"]);
                break;
            }
        }


        
        [[HSQLManager shareSqlMg].songDB close];
    }

    
    if(path && [path isEqualToString:@""]){
    
        path=nil;
    }
    
    return path;

}

#pragma  mark -----------------------插入---------




//插入playlist,songs
+(void)insertPlaylist:(HPlaylistItem*)pitem songs:(NSArray*)arr{

    if ([[HSQLManager shareSqlMg].songDB open]) {

            //1.播放列表的syncid在外面处理,CURRENT_TIMESTAMP

           BOOL res=[[HSQLManager shareSqlMg].songDB executeUpdate:@"INSERT INTO Playlist(name,syncID,folderPath) VALUES (?,?,?)",
                     pitem.name,
                     pitem.syncid,
                     CHECKSTR(pitem.folderPath)];
            //2.插入歌曲
            if (res) {
                [HSQLManager batchInsertSongsWithPlaylist:pitem songs:arr db:[HSQLManager shareSqlMg].songDB];
            }

        }
}



//批量插入播放列表下的歌曲
+(void)batchInsertSongsWithPlaylist:(HPlaylistItem*)pitem songs:(NSArray*)arr db:(id)db{
    
    if (!arr) {
        return;
    }
    [db beginTransaction];
    
    for (HSongItem * item in arr) {
        
        
    //插入到自定义的playlis前检查是否有重复
    FMResultSet * rs = [[HSQLManager shareSqlMg].songDB executeQuery:@"select ID from Song where playListSyncID=? AND localPath=?",pitem.syncid,item.localPath];
        BOOL isexist=NO;
    while ([rs next]) {

        isexist =YES;
    }
    
        if (isexist) {continue;}
    
        [db executeUpdate:@"insert into Song (songType,localPath,urlPath,songName,songDuration,lyricsLocalPath,albumLocalPath,islike,playListSyncID,syncID,trackInfo,album,artist,bookmark) values (?, ?, ?, ?, ?,?, ?, ?, ?, ?,?,?,?,?)" ,
         CHECKSTR(item.songType),
         CHECKSTR(item.localPath),
         CHECKSTR(item.urlPath),
         CHECKSTR(item.songName),
        [NSString stringWithFormat:@"%ld",(long)item.songDuration]  ,
         CHECKSTR(item.lyricsLocalPath),
         CHECKSTR(item.albumLocalPath),
         [NSNumber numberWithBool:item.islike],
         pitem.syncid,
         [HCoreConfig generateSyncID],
         CHECKSTR(item.trackInfo),
         CHECKSTR(item.album),
         CHECKSTR(item.artist),
         item.bookmark];
    }
    
    
    [db commit];
    
    
}

//批量插入播放列表下的歌曲
+(void)batchInsertSongsWithPlaylist:(HPlaylistItem*)pitem songs:(NSArray*)arr{
    
  if ([[HSQLManager shareSqlMg].songDB open]) {
        [[HSQLManager shareSqlMg].songDB beginTransaction];
        //HPlaylistItem的syncid在外面设置
        
        
        [[HSQLManager shareSqlMg].songDB commit];

    }
    
}


//插入歌曲信息,歌词
+(void)insertSonginfoWithSongItem:(HSongItem*)item andInfo:(NSDictionary*)dic{

    
    if ([[HSQLManager shareSqlMg].songDB open]) {

        // 歌词
        NSString * lyricpath=CHECKSTR(dic[@"lyricsLocalPath"]);
        
        
        //插入前先检查是否有相关记录
        FMResultSet * rs =nil;
        
        if (![item.localPath isEqualToString:@""]) {
            rs = [[HSQLManager shareSqlMg].songDB executeQuery:@"select ID from SongInfo where localPath=? ",item.localPath];
        } else if (![item.urlPath isEqualToString:@""]) {
            rs = [[HSQLManager shareSqlMg].songDB executeQuery:@"select ID from SongInfo where urlPath=? ",item.urlPath];
        }
        
        
        BOOL isexist=NO;
        while ([rs next]) {
            
            isexist =YES;
        }
        
        //update
        if (isexist) {
        
            if (![item.localPath isEqualToString:@""]) {
                [[HSQLManager shareSqlMg].songDB  executeUpdate:@"update SongInfo set lyricsLocalPath=? where localPath=?" ,
                 lyricpath,
                 CHECKSTR(item.localPath)];
            } else if (![item.urlPath isEqualToString:@""]) {
                [[HSQLManager shareSqlMg].songDB  executeUpdate:@"update SongInfo set lyricsLocalPath=? where urlPath=?" ,
                 lyricpath,
                 CHECKSTR(item.urlPath)];
            }
            
            
        }
        //insert
        else {
            [[HSQLManager shareSqlMg].songDB  executeUpdate:@"insert into SongInfo (songType,localPath,urlPath,lyricsLocalPath,albumLocalPath,trackInfo) values (?, ?, ?, ?, ?,?)" ,
             CHECKSTR(item.songType),
             CHECKSTR(item.localPath),
             CHECKSTR(item.urlPath),
             lyricpath,
             CHECKSTR(item.albumLocalPath),
             CHECKSTR(item.trackInfo)];

        }
        [[HSQLManager shareSqlMg].songDB close];
    }



}


#pragma  mark -----------------------更新---------

//更改app版本号

//更新playlist
+(BOOL)updatePlaylistWithItem:(HPlaylistItem*)pitem{

    BOOL res=NO;
    
    if ([[HSQLManager shareSqlMg].songDB open]) {

         res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"update Playlist set name=? ,folderPath=? where syncID=?",pitem.name,CHECKSTR( pitem.folderPath),pitem.syncid];
        if (!res) {
           
        } else {
            debugLog(@"succ to update data in Playlist");
        }

        [[HSQLManager shareSqlMg].songDB close];
    }

    return res;
}

//更新歌曲名字



//更新歌的收藏
+(BOOL)updateLikeStateWithItem:(HSongItem*)sitem{
    
    BOOL res=NO;
    
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        
        
        res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"update Song set islike=? where localPath=?",[NSNumber numberWithBool:sitem.islike],CHECKSTR( sitem.localPath)];
        if (!res) {
            
        } else {
            debugLog(@"succ to update islike in Song");
        }
        
        [[HSQLManager shareSqlMg].songDB close];
    }
    
    return res;
}

//更新歌的名称
+(BOOL)updateSongNameWithItem:(HSongItem*)sitem{
    
    BOOL res=NO;
    
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        
        
        res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"update Song set songName=? where syncID=?",sitem.songName, sitem.syncid];
        if (!res) {
            
        } else {
            debugLog(@"succ to update name in Song");
        }
        
        [[HSQLManager shareSqlMg].songDB close];
    }
    
    return res;
}




//更新歌的名称,用于itunes的修改同步到数据库
+(BOOL)updateSongName:(NSString*)name path:(NSString*)newpath oldPath:(NSString*)oldpath{
    
    BOOL res=NO;
    
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        
        
        res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"update Song set songName=? , localPath=? where localPath=?",name,newpath,oldpath];
        if (!res) {
            
        } else {
            debugLog(@"succ to update name in Song");
        }
        
        
        
        //还要修改歌词信息
        
        res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"update SongInfo set localPath=? where localPath=?",newpath,oldpath];
        if (!res) {
            
        } else {
            debugLog(@"succ to update name in Song");
        }
        
        
        [[HSQLManager shareSqlMg].songDB close];
    }
    
    return res;
}


//更新sandbox的urldata
+(void)updateBookmakrDataWithSongItem:(HSongItem*)songitem{

    NSString * localPath=CHECKSTR(songitem.localPath) ;

    if (![localPath isEqualToString:@""]) {
        
        NSURL * fileurl=[NSURL fileURLWithPath:localPath];
        
        NSError * bookmarkerror=nil;
        NSData * data =[fileurl bookmarkDataWithOptions:NSURLBookmarkCreationWithSecurityScope
                         includingResourceValuesForKeys:nil
                                          relativeToURL:nil
                                                  error:&bookmarkerror];
        
        
        if (bookmarkerror) {
           // debugLog(@"bookmarkerror:%@",bookmarkerror.description);
        } else {
            songitem.bookmark=data;
            
            
            if ([[HSQLManager shareSqlMg].songDB open]) {

              BOOL res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"update Song set bookmark=? where localPath=?",data,localPath];
//                if (!res) {
//                    
//                } else {
//                    debugLog(@"succ to update name in Song");
//                }
//                
                [[HSQLManager shareSqlMg].songDB close];
            }

            
            
            
        }

        
        
    }

    
}


#pragma  mark------------删除---------

//删除播放列表+旗下的歌曲
+(BOOL)deletePlaylistWithItem:(HPlaylistItem*)pitem{


    BOOL res=NO;
    
    if ([[HSQLManager shareSqlMg].songDB open]) {
        
        //1.删除播放列表
        res = [[HSQLManager shareSqlMg].songDB executeUpdate:@"delete from Playlist where syncID=?",pitem.syncid];

        //再删除旗下歌曲
        [[HSQLManager shareSqlMg].songDB executeUpdate:@"delete from Song  where playListSyncID=?",pitem.syncid];

        [[HSQLManager shareSqlMg].songDB close];
    }
    
    return res;
}


//批量删除歌曲
+(void)batchDeleteSongsWithSongItems:(NSArray*)arr{
    
    if ([[HSQLManager shareSqlMg].songDB open]) {
        [[HSQLManager shareSqlMg].songDB beginTransaction];

        
        for (HSongItem * songItem in arr) {
            
           [[HSQLManager shareSqlMg].songDB executeUpdate:@"delete from Song  where syncID=?",songItem.syncid];
            
        }
        
        
        [[HSQLManager shareSqlMg].songDB commit];

        [[HSQLManager shareSqlMg].songDB close];
    }
    
}

//批量删除歌曲-根据歌曲路径
+(void)batchDeleteSongsHasPathWithSongItems:(NSArray*)arr{
    
    if ([[HSQLManager shareSqlMg].songDB open]) {
        [[HSQLManager shareSqlMg].songDB beginTransaction];
        
        
        for (HSongItem * songItem in arr) {
            
            [[HSQLManager shareSqlMg].songDB executeUpdate:@"delete from Song  where localPath=?",songItem.localPath];
            
        }
        
        
        [[HSQLManager shareSqlMg].songDB commit];
        
        [[HSQLManager shareSqlMg].songDB close];
    }
    
}


//删除歌曲歌词信息
+(void)removeThelyricInfoWithSongItem:(HSongItem*)songitem{

    if (songitem) {
    if ([[HSQLManager shareSqlMg].songDB open]) {
        NSString * lyricPath=nil;
        //1.先查询是否有歌词
         NSString * sql=@"";
        if (![songitem.localPath isEqualToString:@""]) {
            sql=[NSString stringWithFormat:@"SELECT lyricsLocalPath FROM SongInfo where localPath='%@'",songitem.localPath];
        } else if (![songitem.urlPath isEqualToString:@""]) {
            sql=[NSString stringWithFormat:@"SELECT lyricsLocalPath FROM SongInfo where urlPath='%@'",songitem.urlPath];
        }
        
      
        FMResultSet * rs = [[HSQLManager shareSqlMg].songDB executeQuery:sql];
        
        while ([rs next]) {
            lyricPath =CHECKSTR([rs stringForColumn:@"lyricsLocalPath"]);
            break;
        }

        //2.删除歌词文件
        if(lyricPath) {
            if([[NSFileManager defaultManager] fileExistsAtPath:lyricPath]){
            
                [[NSFileManager defaultManager] removeItemAtPath:lyricPath error:nil];
            
            }
            
            //3. update
            
            if (![songitem.localPath isEqualToString:@""]) {
                sql=[NSString stringWithFormat:@"update SongInfo set lyricsLocalPath='' where localPath='%@'",songitem.localPath];
            } else if (![songitem.urlPath isEqualToString:@""]) {
                sql=[NSString stringWithFormat:@"update SongInfo set lyricsLocalPath='' where urlPath='%@'",songitem.urlPath];
            }
            BOOL res = [[HSQLManager shareSqlMg].songDB executeUpdate:sql];
            if (!res) {
                
            } else {
                debugLog(@"succ to update name in SongInfo");
            }

        }

        [[HSQLManager shareSqlMg].songDB close];
    }
 }
}


-(void)beginTransactionSongDB{
     [[HSQLManager shareSqlMg].songDB beginTransaction];
}

-(void)commitSongDB{
 [[HSQLManager shareSqlMg].songDB commit];

}

@end
